#########################
#   Author : Lion yu    #
#   Date : 2023/05/08   #
#   Id : SM2772         #
#########################
import os
from handle import Handle, Log, DBConf
from tbUtils.getResp import *
from sqlalchemy import create_engine
import sqlalchemy

# 写入数据解析
def InsertData_analysis(data, table):
    if len(data) == 0:
        Log.warning('无数据写入')
        return
    keys = list(data[0].keys())
    keys.sort(reverse=True)
    key = ",".join(keys)
    key = f"({key})"
    n = 0 
    step = 100
    while 1:
        values_str = ""
        data_tmp = data[n:n+step]
        if len(data_tmp) == 0:
            break
        values = []
        for i in data_tmp:
            value_tmp1 = []
            value_tmp2 = [i[index] for index in keys]
            for vv in value_tmp2:
                if '"' in str(vv):
                    vv = str(vv).replace('"', '`')
                value_tmp1.append(vv)
            values.append(value_tmp1)
        for i in values:
            v = ",".join([f"""\"{str(i_v)}\"""" for i_v in i])
            v = f"({v})"
            values_str = values_str + v + ","
        values_str = values_str[:-1]
        try:
            Handle.db_insert(table=table, column=key, value=values_str)
        except Exception as e:
            Log.error(f"INSERT INTO {table} {key} VALUES {values_str}\n")
            Log.error(e)
        n += step
    return True

# 项目数据写入
def ProjectQuery_DataWrite(tb_name:str="project"):
    data:pd.DataFrame = allResp_ProjectQuery()
    try:
        Df_toSql(df=data, table_name=tb_name)
    except Exception as e:
        Log.error(f"项目数据 写入失败 \n ==> {e}")
    return
    
# 任务数据写入  
def ProjectTaskQuery_DataWrite(projectId, tb_name:str='task'):
    taskdata_filepath = os.path.join(Handle.taskData_path, f"{projectId}.pkl")
    data:pd.DataFrame = pd.read_pickle(taskdata_filepath)
    data['taskId'] = data.index
    data.fillna("", inplace=True)
    data.drop_duplicates(subset=['taskId'], keep='last', inplace=True)
    # data = Handle.readcsv2DF(filepath=taskdata_filepath, is_fillna=True, is_distinct=True, subset=['taskId'])
    data.drop(columns='created', inplace=True)
    data['parentTaskId'].fillna("", inplace=True)
    data.set_index('taskId', inplace=True)
    try:
        Df_toSql(data, table_name=tb_name)
    except Exception as e:
        Log.error(f"项目 {projectId} task 写入失败 \n ==> {e}")
    return
    
    
# 任务类型写入
def ScenarioFieldConfigSearch_DataWrite(projectId, table_name:str='task_type_info'):
    data:list = resp_ScenarioFieldConfigSearch(projectId)
    try:
        InsertData_analysis(data, table_name)
    except Exception as e:
        Log.error(f"任务类型写入失败!\n ==> {e}")
    return

# 项目缺陷等级写入
def CustomfieldSearch_DataWrite(projectId, sfcId, q, table_name:str='bugs_level_info'):
    data:list = SimpleResp_CustomfieldSearch(projectId=projectId, sfcId=sfcId, q=q)
    try:
        InsertData_analysis(data, table_name)
    except Exception as e:
        Log.error(f"项目缺陷等级写入失败!\n ==> {e}")
    return

# Bug快照数据写入
def AllTaskActivity_DataWrite(projectId, tb_name="bugs_snapshot_ext"):
    bugSnapshot_filepath = os.path.join(Handle.bugSnapshotData_path, f"{projectId}.pkl")
    # data = pd.read_csv(bugSnapshot_filepath, index_col=False)
    # data = Handle.readcsv2DF(filepath=bugSnapshot_filepath, is_fillna=True, is_distinct=True, subset=['bugId'])
    data:pd.DataFrame = pd.read_pickle(bugSnapshot_filepath)
    data.fillna("", inplace=True)
    data.drop_duplicates(subset=['bugId'], keep='last', inplace=True)
    data.set_index('bugId', inplace=True)
    try:
        Df_toSql(data, table_name=tb_name)
    except Exception as e:
        Log.error(f"项目 {projectId} Bug快照数据写入失败 \n ==> {e}")
    return

# Bug统计写入
def statisticsData_DataWrite(projectId, tb_name:str='bugs_statistics_ext'):
    statistics_filepath = os.path.join(Handle.statistics2Month_path, f"{projectId}.pkl")
    data:pd.DataFrame = pd.read_pickle(statistics_filepath)
    data.fillna("", inplace=True)
    # data = Handle.readcsv2DF(filepath=statistics_filepath, is_fillna=True)
    data.set_index('Month', inplace=True)
    try:
        Df_toSql(data, table_name=tb_name)
    except Exception as e:
        Log.error(f"项目 {projectId} Bug统计写入失败 \n ==> {e}")
    return
    
# 项目自定义字段写入
def ProjectColumns_DataWrite(projectId, table_name:str='projectId_columns_ext'):
    data:list = ProjectId_columns(projectId=projectId)
    try:
        InsertData_analysis(data, table_name)
    except Exception as e:
        Log.error(f"项目 {projectId} Bug 与 tfsId映射 写入失败!\n ==> {e}")
    return

    
# 项目Bug等级sfcId写入
def BugSfcId_DataWrite(projectId, table_name:str='bugSfcId'):
    data:list = get_BugSfcId(projectId=projectId)
    try:
        InsertData_analysis(data, table_name)
    except Exception as e:
        Log.error(f"项目 {projectId} Bug等级sfcId 写入失败!\n ==> {e}")
    return
    
def Df_toSql(df:pd.DataFrame, table_name:str, dtype:dict={}, if_exists="append"):
    host = DBConf['host']
    port = DBConf['port']
    user = DBConf['user']
    pwd = DBConf['password']
    database_name = DBConf['database']
    engine = f"mysql+pymysql://{user}:{pwd}@{host}:{port}/{database_name}?charset=utf8mb4"
    conn = create_engine(engine)
    df.to_sql(table_name, con=conn, if_exists=if_exists, chunksize=10000)
    
# bug写入
def ProjectBugQuery_DataWrite(projectId, tb_name:str='bugs_ext'):
    bug_filepath = os.path.join(Handle.bugData_path, f"{projectId}.pkl")
    data:pd.DataFrame = pd.read_pickle(bug_filepath)
    data.fillna("", inplace=True)
    data.drop_duplicates(subset=['taskId'], keep='last', inplace=True)
    # data = Handle.readcsv2DF(filepath=bug_filepath, is_fillna=True, is_distinct=True, subset=['taskId'])
    data.set_index('taskId', inplace=True)
    try:
        Df_toSql(data, table_name=tb_name)
    except Exception as e:
        Log.error(f"项目 {projectId} bug写入失败 \n ==> {e}")
    return
        
# 企业部门、Id映射写入
def OrgDepartments_DataWrite(tb_name:str='org_departments_ext'):
    df = get_OrgDepartments()
    try:
        Df_toSql(df, table_name=tb_name)
    except Exception as e:
        Log.error(f"企业部门、Id映射 写入失败 \n ==> {e}")
    return
    
# 企业部门成员写入
def DepartmentMembers_DataWrite(tb_name:str="org_department_members_ext"):
    df = get_DepartmentMembers()
    try:
        Df_toSql(df, table_name=tb_name)
    except Exception as e:
        Log.error(f"企业部门成员写入 写入失败 \n ==> {e}")
    return

# Bug 周维度统计写入
def statistics2Week_DataWrite(projectId, tb_name:str='bugs_statistics2week'):
    statistics2Week_filepath = os.path.join(Handle.statistics2Week_path, f"{projectId}.pkl")
    data:pd.DataFrame = pd.read_pickle(statistics2Week_filepath)
    data.fillna("", inplace=True)
    # data = Handle.readcsv2DF(filepath=statistics2Week_filepath, is_fillna=True)
    data.set_index('week', inplace=True)
    try:
        Df_toSql(data, tb_name)
    except Exception as e:
        Log.error(f"Bug 周维度 写入失败 \n ==> {e}")